﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Data.Common;
using System.Data;

using System.Configuration;

namespace WindowsFormsApplication1
{
    public class DatabaseConnection : DbConnection
    {

        DbConnection connection = null;
        ConnectionState state = ConnectionState.Closed;

        public DatabaseConnection(string connectString):this("",connectString) {}        

        public DatabaseConnection(string providerName, string connectString):base()
        {
            if (providerName == null || providerName.Trim() == "")
                providerName = "System.Data.OleDb";
            
            connection = CreateDbConnection(providerName, connectString);
            
            //return null;
        }        

        /// <summary>
        /// Given a provider name and connection string, 
        /// create the DbProviderFactory and DbConnection.
        /// Returns a DbConnection on success; null on failure.
        /// System.Data.SqlClient
        /// System.Data.Odbc Namespace
        /// System.Data.OleDb Namespace
        /// </summary>
        /// <param name="providerName"></param>
        /// <param name="connectionString"></param>
        /// <returns></returns>
        public DbConnection CreateDbConnection(
            string providerName, string connectionString)
        {
            // Assume failure.
            //DbConnection connection = null;
            
            // Create the DbProviderFactory and DbConnection.
            if (connectionString != null)
            {
                try
                {
                    DbProviderFactory factory =
                        DbProviderFactories.GetFactory(providerName);

                    connection = factory.CreateConnection();
                    connection.ConnectionString = connectionString;
                }
                catch (Exception ex)
                {
                    // Set the connection to null if it was created.
                    if (connection != null)
                    {
                        connection = null;
                    }
                    Console.WriteLine(ex.Message);
                }
            }
            // Return the connection.
            return connection;
        }

        /// <summary>
        /// Retrieve a connection string by specifying the providerName.
        /// Assumes one connection string per provider in the config file.
        /// </summary>
        /// <param name="providerName"></param>
        /// <returns></returns>
        static string GetConnectionStringByProvider(string providerName)
        {
            // Return null on failure.
            string returnValue = null;

            // Get the collection of connection strings.
            
            ConnectionStringSettingsCollection settings =
                ConfigurationManager.ConnectionStrings;

            // Walk through the collection and return the first 
            // connection string matching the providerName.
            if (settings != null)
            {
                foreach (ConnectionStringSettings cs in settings)
                {
                    if (cs.ProviderName == providerName)
                        returnValue = cs.ConnectionString;
                    break;
                }
            }
            return returnValue;
        }

        /// <summary>
        /// Listing the Installed Provider Factory Classes
        /// This example assumes a reference to System.Data.Common.
        /// http://msdn.microsoft.com/en-us/library/dd0w4a2z.aspx
        /// </summary>
        /// <returns></returns>
        static DataTable GetProviderFactoryClasses()
        {
            // Retrieve the installed providers and factories.

            DataTable table = DbProviderFactories.GetFactoryClasses();

            // Display each row and column value.

            foreach (DataRow row in table.Rows)
            {
                foreach (DataColumn column in table.Columns)
                {
                    Console.WriteLine(row[column]);
                }
            }
            return table;
        }

        protected override DbTransaction BeginDbTransaction(IsolationLevel isolationLevel)
        {
            throw new NotImplementedException();
        }

        public override void ChangeDatabase(string databaseName)
        {
            throw new NotImplementedException();
        }

        public override void Close()
        {
            try
            {
                connection.Close();
                state = ConnectionState.Closed;
            }
            catch (Exception ex)
            {
                
                throw;
            }
        }

        public override string ConnectionString
        {
            get
            {
                return connection.ConnectionString;
            }
            set
            {
                connection.ConnectionString = value;
            }
        }

        protected override DbCommand CreateDbCommand()
        {
            throw new NotImplementedException();
        }

        public override string DataSource
        {
            get { throw new NotImplementedException(); }
        }

        public override string Database
        {
            get { throw new NotImplementedException(); }
        }

        public override void Open()
        {
            connection.Open();
            state = ConnectionState.Open;
        }

        public override string ServerVersion
        {
            get { throw new NotImplementedException(); }
        }

        public override ConnectionState State
        {
            get { return state; }
        }

        public override DataTable GetSchema(string collectionName, string[] restrictionValues)
        {
            return base.GetSchema(collectionName, restrictionValues);
        }

        public override DataTable GetSchema(string collectionName)
        {
            //return base.GetSchema(collectionName);
            return connection.GetSchema(collectionName); 
        }

        /// <summary>
        /// Retorna o nome do provedor ,NET a partir da string de conexao
        /// </summary>
        /// <param name="connectionString">String de conexão ao banco de dados</param>
        public static string GetProviderName(string connectionString)
        {
            string providerName = "System.Data.OleDb";

            try
            {
                DbConnectionStringBuilder connectionBuilder = new DbConnectionStringBuilder();
                connectionBuilder.ConnectionString = connectionString;

                object providerObj = null;
                connectionBuilder.TryGetValue("Provider", out providerObj);
                providerName = providerObj.ToString();
            }
            catch (Exception ex)
            {                
                throw;
            }

            return providerName;
        }
    }
}


//Retrieving Provider Information
//--------------------------------------------------------------------------------

//You can retrieve information about all of the data providers installed on the local computer by using the GetFactoryClasses method. It returns a DataTable named DbProviderFactories that contains the columns described in the following table. 

//Column ordinal
// Column name
// Example output
// Description
 
//0
// Name
// SqlClient Data Provider
// Readable name for the data provider
 
//1
// Description
// .Net Framework Data Provider for SqlServer
// Readable description of the data provider
 
//2
// InvariantName
// System.Data.SqlClient
// Name that can be used programmatically to refer to the data provider
 
//3
// AssemblyQualifiedName
// System.Data.SqlClient.SqlClientFactory, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089
// Fully qualified name of the factory class, which contains enough information to instantiate the object
 

//This DataTable can be used to enable a user to select a DataRow at run time. The selected DataRow can then be passed to the GetFactory method to create a strongly typed DbProviderFactory. A selected DataRow can be passed to the GetFactory method to create the desired DbProviderFactory object. 


/*
<configuration>
  <connectionStrings>
    <clear/>
    <add name="NorthwindSQL" 
     providerName="System.Data.SqlClient" 
     connectionString=
     "Data Source=MSSQL1;Initial Catalog=Northwind;Integrated Security=true"
    />

    <add name="NorthwindAccess" 
     providerName="System.Data.OleDb" 
     connectionString=
     "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Data\Northwind.mdb;"
    />
  </connectionStrings>
</configuration>

*/